-- 创建 SEQUENCE
CREATE SEQUENCE XXL_JOB_INFO_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE XXL_JOB_LOG_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE XXL_JOB_LOG_REPORT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE XXL_JOB_LOGGLUE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE XXL_JOB_REGISTRY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE XXL_JOB_GROUP_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE XXL_JOB_USER_SEQ START WITH 1 INCREMENT BY 1;

-- 创建表
CREATE TABLE XXL_JOB_INFO (
  ID NUMBER(11) NOT NULL,
  JOB_GROUP NUMBER(11) NOT NULL,
  JOB_DESC VARCHAR2(255 CHAR) NOT NULL,
  ADD_TIME TIMESTAMP DEFAULT NULL,
  UPDATE_TIME TIMESTAMP DEFAULT NULL,
  AUTHOR VARCHAR2(64 CHAR) DEFAULT NULL,
  ALARM_EMAIL VARCHAR2(255 CHAR) DEFAULT NULL,
  SCHEDULE_TYPE VARCHAR2(50 CHAR) DEFAULT 'NONE' NOT NULL,
  SCHEDULE_CONF VARCHAR2(128 CHAR) DEFAULT NULL,
  MISFIRE_STRATEGY VARCHAR2(50 CHAR) DEFAULT 'DO_NOTHING' NOT NULL,
  EXECUTOR_ROUTE_STRATEGY VARCHAR2(50 CHAR) DEFAULT NULL,
  EXECUTOR_HANDLER VARCHAR2(255 CHAR) DEFAULT NULL,
  EXECUTOR_PARAM VARCHAR2(512 CHAR) DEFAULT NULL,
  EXECUTOR_BLOCK_STRATEGY VARCHAR2(50 CHAR) DEFAULT NULL,
  EXECUTOR_TIMEOUT NUMBER(11) DEFAULT 0 NOT NULL,
  EXECUTOR_FAIL_RETRY_COUNT NUMBER(11) DEFAULT 0 NOT NULL,
  GLUE_TYPE VARCHAR2(50 CHAR) NOT NULL,
  GLUE_SOURCE CLOB,
  GLUE_REMARK VARCHAR2(128 CHAR) DEFAULT NULL,
  GLUE_UPDATETIME TIMESTAMP DEFAULT NULL,
  CHILD_JOBID VARCHAR2(255 CHAR) DEFAULT NULL,
  TRIGGER_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
  TRIGGER_LAST_TIME NUMBER(13) DEFAULT 0 NOT NULL,
  TRIGGER_NEXT_TIME NUMBER(13) DEFAULT 0 NOT NULL,
  PRIMARY KEY (ID)
);

--CREATE OR REPLACE TRIGGER TRG_XXL_JOB_INFO
--  BEFORE INSERT ON XXL_JOB_INFO
--  FOR EACH ROW
--BEGIN
--  SELECT XXL_JOB_INFO_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
--END;
--/

CREATE TABLE XXL_JOB_LOG (
  ID NUMBER(20) NOT NULL,
  JOB_GROUP NUMBER(11) NOT NULL,
  JOB_ID NUMBER(11) NOT NULL,
  EXECUTOR_ADDRESS VARCHAR2(255 CHAR) DEFAULT NULL,
  EXECUTOR_HANDLER VARCHAR2(255 CHAR) DEFAULT NULL,
  EXECUTOR_PARAM VARCHAR2(512 CHAR) DEFAULT NULL,
  EXECUTOR_SHARDING_PARAM VARCHAR2(20 CHAR) DEFAULT NULL,
  EXECUTOR_FAIL_RETRY_COUNT NUMBER(11) DEFAULT 0 NOT NULL,
  TRIGGER_TIME TIMESTAMP DEFAULT NULL,
  TRIGGER_CODE NUMBER(11) DEFAULT NULL,
  TRIGGER_MSG CLOB,
  HANDLE_TIME TIMESTAMP DEFAULT NULL,
  HANDLE_CODE NUMBER(11) DEFAULT NULL,
  HANDLE_MSG CLOB,
  ALARM_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (ID)
);

--CREATE OR REPLACE TRIGGER TRG_XXL_JOB_LOG
--  BEFORE INSERT ON XXL_JOB_LOG
--  FOR EACH ROW
--BEGIN
--  SELECT XXL_JOB_LOG_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
--END;
--/

CREATE TABLE XXL_JOB_LOG_REPORT (
  ID NUMBER(11) NOT NULL,
  TRIGGER_DAY TIMESTAMP DEFAULT NULL,
  RUNNING_COUNT NUMBER(11) DEFAULT 0 NOT NULL,
  SUC_COUNT NUMBER(11) DEFAULT 0 NOT NULL,
  FAIL_COUNT NUMBER(11) DEFAULT 0 NOT NULL,
  UPDATE_TIME TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (ID),
  UNIQUE (TRIGGER_DAY)
);

CREATE OR REPLACE TRIGGER TRG_XXL_JOB_LOG_REPORT
  BEFORE INSERT ON XXL_JOB_LOG_REPORT
  FOR EACH ROW
BEGIN
  SELECT XXL_JOB_LOG_REPORT_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE XXL_JOB_LOGGLUE (
  ID NUMBER(11) NOT NULL,
  JOB_ID NUMBER(11) NOT NULL,
  GLUE_TYPE VARCHAR2(50 CHAR) DEFAULT NULL,
  GLUE_SOURCE CLOB,
  GLUE_REMARK VARCHAR2(128 CHAR) NOT NULL,
  ADD_TIME TIMESTAMP DEFAULT NULL,
  UPDATE_TIME TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER TRG_XXL_JOB_LOGGLUE
  BEFORE INSERT ON XXL_JOB_LOGGLUE
  FOR EACH ROW
BEGIN
  SELECT XXL_JOB_LOGGLUE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE XXL_JOB_REGISTRY (
  ID NUMBER(11) NOT NULL,
  REGISTRY_GROUP VARCHAR2(50 CHAR) NOT NULL,
  REGISTRY_KEY VARCHAR2(255 CHAR) NOT NULL,
  REGISTRY_VALUE VARCHAR2(255 CHAR) NOT NULL,
  UPDATE_TIME TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER TRG_XXL_JOB_REGISTRY
  BEFORE INSERT ON XXL_JOB_REGISTRY
  FOR EACH ROW
BEGIN
  SELECT XXL_JOB_REGISTRY_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE INDEX I_G_K_V ON XXL_JOB_REGISTRY (REGISTRY_GROUP, REGISTRY_KEY, REGISTRY_VALUE);

CREATE TABLE XXL_JOB_GROUP (
  ID NUMBER(11) NOT NULL,
  APP_NAME VARCHAR2(64 CHAR) NOT NULL,
  TITLE VARCHAR2(12 CHAR) NOT NULL,
  ADDRESS_TYPE NUMBER(1) DEFAULT 0 NOT NULL,
  ADDRESS_LIST CLOB,
  UPDATE_TIME TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER TRG_XXL_JOB_GROUP
  BEFORE INSERT ON XXL_JOB_GROUP
  FOR EACH ROW
BEGIN
  SELECT XXL_JOB_GROUP_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE XXL_JOB_USER (
  ID NUMBER(11) NOT NULL,
  USERNAME VARCHAR2(50 CHAR) NOT NULL,
  PASSWORD VARCHAR2(50 CHAR) NOT NULL,
  ROLE NUMBER(1) NOT NULL,
  PERMISSION VARCHAR2(255 CHAR) DEFAULT NULL,
  PRIMARY KEY (ID),
  UNIQUE (USERNAME)
);

CREATE OR REPLACE TRIGGER TRG_XXL_JOB_USER
  BEFORE INSERT ON XXL_JOB_USER
  FOR EACH ROW
BEGIN
  SELECT XXL_JOB_USER_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE XXL_JOB_LOCK (
  LOCK_NAME VARCHAR2(50 CHAR) NOT NULL,
  PRIMARY KEY (LOCK_NAME)
);

-- 插入数据
INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list, update_time) VALUES (xxl_job_group_seq.NEXTVAL, 'fs-schedule-exec', '日程执行器', 0, NULL, TO_TIMESTAMP('2024-08-06 22:21:31', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list, update_time) VALUES (xxl_job_group_seq.NEXTVAL, 'xxl-job-executor-sample', '示例执行器', 0, NULL, TO_TIMESTAMP('2024-08-06 22:21:31', 'YYYY-MM-DD HH24:MI:SS'));


INSERT INTO xxl_job_info (id, job_group, job_desc, add_time, update_time, author, alarm_email, schedule_type, schedule_conf, misfire_strategy, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid) VALUES (xxl_job_info_seq.NEXTVAL, 1, '测试任务1', TO_TIMESTAMP('2018-11-03 22:21:31', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2018-11-03 22:21:31', 'YYYY-MM-DD HH24:MI:SS'), 'XXL', '', 'CRON', '0 0 0 * * ? *', 'DO_NOTHING', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', NULL, 'GLUE代码初始化', TO_TIMESTAMP('2018-11-03 22:21:31', 'YYYY-MM-DD HH24:MI:SS'), '');
INSERT INTO xxl_job_user (id, username, password, role, permission) VALUES (xxl_job_user_seq.NEXTVAL, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO xxl_job_lock (lock_name) VALUES ('schedule_lock');

COMMIT;
